package dancereal;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class J03_MemberDAO implements J03_IMember_DAO {

	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:realdance/realdance@112.216.253.125:1521:xe";
	private String insertSQL = "insert into J03Member values (exam.nextval,?,?,?,?,sysdate)";
	private String selectSQL = "select * from J03Member order by mno desc";
	private String updateSQL = "update J03Member set passwd=?, name=?, email =?, regdate = sysdate where mno = ?";
	private String deleteSQL = "delete J03Member where mno = ?";
			
	@Override
	public boolean insertMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			// ? 매개 변수에 대입할 값 지정
			pstmt.setString(1, m.getUserid());
			pstmt.setString(2, m.getPasswd());
			pstmt.setString(3, m.getName());
			pstmt.setString(4, m.getEmail());
			pstmt.executeUpdate();
			result = true;
			
		}catch(Exception ex){	
			ex.printStackTrace();
		}finally{
			closeDatabase(conn,pstmt,null);
		 
		}
		return result;
	}// insertMember
		
		// JDBC Connection 객체를 생성하는 메서드
		
		private Connection openDatabase() {
		try{
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		}catch(Exception ex){
			ex.printStackTrace();
		}
			return null;
		}// JDBC 객체를 닫는 메서드	
		private void closeDatabase
		(Connection c,PreparedStatement p, ResultSet r)
		{
			if(r != null) 
			try {r.close(); r = null;}
		catch(Exception e){}
		 	if(p != null) 
			try {p.close(); p = null;}
		catch(Exception e){}
		 	if(c != null) 
			try {c.close(); c = null;}
			catch(Exception e){}
		}
		// Select 문으로 전체 회원정보를 조회한 후
		// MemberVO를 내용으로 가지는
		// 동적배열 List 로 결과를 넘김
		@Override
		public List<J03_MemberVO> selectMember() {
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			List<J03_MemberVO> result = new ArrayList<>(); 
					
				try{
					
				conn = openDatabase();
				pstmt = conn.prepareStatement(selectSQL);
				rs = pstmt.executeQuery();
				while(rs.next()){
					J03_MemberVO m = new J03_MemberVO();
					m.setMno(rs.getString("mno"));
					m.setUserid(rs.getString("userid"));
					m.setPasswd(rs.getString("passwd"));
					m.setName(rs.getString("name"));
					m.setEmail(rs.getString("email"));
					m.setRegdate(rs.getString("regdate"));
					result.add(m);
					
				}
			}catch(Exception ex){
				ex.printStackTrace();
			}finally{
				closeDatabase(conn, pstmt, rs);
			
		}
			return result;

			
	
}

		@Override
		public boolean updateMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
			
		try{ conn = openDatabase();
			pstmt = conn.prepareStatement(updateSQL);
			 pstmt.setString(1, m.getPasswd());
			 pstmt.setString(2, m.getName());
			 pstmt.setString(3, m.getEmail());
			 pstmt.setString(4, m.getMno());
			 int cnt = pstmt.executeUpdate();
			 if(cnt==0) System.out.println(
					 "명령은 실행됐으나, 수정된 데이터는 없습니다");
			 result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, null);
		}
		
		return result;
		}

		@Override
		public boolean deleteMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{conn = openDatabase();
		pstmt = conn.prepareStatement(deleteSQL);
		pstmt.setString(1, m.getMno());
		int cnt = pstmt.executeUpdate();
		if(cnt == 0) System.out.println("명령은 실행 했으나, 삭제된 데이터는 없습니다.");
		result = true;
		}catch(Exception ex){
		ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, null);
		}
		
			
			return result;
		}
		
}
	
